package pro.syj.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import pro.syj.bean.Student;
import pro.syj.db.DBassist;

public class StudentDao {
	/*
	 * 查询所有学生的个人信息
	 * return ArrayList<Student>
	 */
	public ArrayList<Student> selectAll(){
		
		ArrayList<Student> studentlist = new ArrayList<>();
		String sql = "select * from sinfo";
		
		try{
			DBassist dbs = new DBassist();
			ResultSet rss = dbs.query(sql);
			while(rss != null && rss.next()){
				Student student = new Student();
				student.setSid(rss.getString(1));
				student.setPsd(rss.getString(2));
				student.setSname(rss.getString(3));
				student.setGrade(rss.getInt(4));
				student.setMajor(rss.getString(5));
				studentlist.add(student);
			}
		}catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return studentlist;
	}
	
	/*
	 * (学生登录)查看账户密码是否正确
	 * param sid psd
	 * return int
	 */
	public int isValid(String sid,String psd){
		
		String sql = "select * from sinfo where sid = ? and psd = ?";
		
		try{
			DBassist dbs = new DBassist();
			ResultSet rss = dbs.query(sql,sid,psd);
			if(rss != null && rss.next()){
				return 1;
			}else{
				return -1;
			}
		}catch(Exception e){
			e.printStackTrace();
		}
		
		return -1;
	}
	
	/*
	 * (学生登录/管理员管理)获取一个学生
	 * param sid 
	 * return ArrayList<Student>
	 */
	public ArrayList<Student> selectOne(String sid){
		
		
		String sql = "select * from sinfo where sid = ? ";
		ArrayList<Student> studentlist = new ArrayList<>();
		
		try{
			DBassist dbs = new DBassist();
			ResultSet rss = dbs.query(sql,sid);
			if(rss != null && rss.next()){
				Student student = new Student();
				student.setSid(rss.getString(1));
				student.setPsd(rss.getString(2));
				student.setSname(rss.getString(3));
				student.setGrade(rss.getInt(4));
				student.setMajor(rss.getString(5));
				studentlist.add(student); 
			}
		}catch(Exception e){
			e.printStackTrace();
		}
		
		return studentlist;
	}
	
	public Student selectOne2(String sid){
		
		
		String sql = "select * from sinfo where sid = ? ";
		Student student = new Student();
		
		try{
			DBassist dbs = new DBassist();
			ResultSet rss = dbs.query(sql,sid);
			if(rss != null && rss.next()){
				student.setSid(rss.getString(1));
				student.setPsd(rss.getString(2));
				student.setSname(rss.getString(3));
				student.setGrade(rss.getInt(4));
				student.setMajor(rss.getString(5));
			}
		}catch(Exception e){
			e.printStackTrace();
		}
		
		return student;
	}
	
	/*
	 * （学生注册）增加一个学生的信息
	 * param  sid psd sname grade major
	 * return int
	 */
	public int addStudent(String sid,String psd,String sname,int grade,String major){
		
		String sql = "insert into sinfo value(?,?,?,?,?)";
		int flag = -1;
		
		try{
			DBassist dbs = new DBassist();
			flag = dbs.update(sql, sid,psd,sname,grade,major);
			return flag;
		}catch(Exception e){
			e.printStackTrace();
		}
		
		return flag;
	}
	
	/*
	 * （管理员）删除一个学生的信息
	 * param sid
	 * return int
	 */
	public int deleteOne(String sid){
		
		String sql = "delete from sinfo where sid = ?";
		int flag = -1;
		
		try{
			DBassist dbs = new DBassist();
			flag = dbs.update(sql, sid);
			return flag;
		}catch(Exception e){
			e.printStackTrace();
		}
		
		return flag;
	}
	
	/*
	 * 根据sid查询这个学生是否存在
	 * param sid
	 * return int
	 */
	public int isStudent(String sid){
		
		String sql = "select * from sinfo where sid = ?";
		
		try{
			DBassist dbs = new DBassist();
			ResultSet rss = dbs.query(sql, sid);
			if(rss != null){
				return 1;
			}
		}catch(Exception e){
			e.printStackTrace();
		}
		
		return -1;
	}
}

















